﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Web;


namespace HQPad.DAL
{
    /// <summary>
    /// Provides access to an excel spreasheet.
    /// </summary>
    public class ExcelRepository
    {
        private readonly IList<String> ConnectionStrings = new List<string>
        {
            "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"",
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES;\";",
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"HTML Import;HDR=YES;IMEX=1\";"
        };

        public DataTable ReadFile(string fileName, string sheetName)
        {
            var file = ReadFile(fileName);
            return file != null
                       ? file.Tables.Cast<DataTable>().FirstOrDefault(
                           t => t.TableName.Equals(sheetName, StringComparison.OrdinalIgnoreCase))
                       : null;
        }

        public DataSet ReadFile(string fileName)
        {
            var myDataset = new DataSet();

            var connection = GetConnectionForFile(fileName);

            if (connection == null)
                return null;

            using (connection)
            {
                foreach (DataRow row in connection.GetSchema("tables").Rows)
                {
                    string sheetName = row[2].ToString().Replace("''", "'").TrimEnd('_');
                    var tempDataSet = new DataSet();
                    var adapter = new OleDbDataAdapter("SELECT * from [" + sheetName + "]", connection);

                    adapter.Fill(tempDataSet);
                    DataTable dt = tempDataSet.Tables[0].Copy();
                    dt.Namespace = sheetName;
                    myDataset.Tables.Add(dt);
                }
                return myDataset;
            }
        }

        private OleDbConnection GetConnectionForFile(string fileName)
        {
            foreach (var cs in ConnectionStrings)
            {
                var connection = new OleDbConnection(string.Format(cs, fileName));
                try
                {
                    connection.Open();
                    return connection;
                }
                catch (OleDbException)
                {
                    continue;
                }
                catch (InvalidOperationException)
                {
                    //"provider not registered on machine" exceptions. ignore
                    continue;
                }
            }

            return null;
        }

        public string PersistExcelFile(HttpPostedFileBase importDocument, string destinationDirectory)
        {
            if (importDocument == null)
            {
                throw new ArgumentNullException("importDocument");
            }
            if (string.IsNullOrEmpty(destinationDirectory))
            {
                throw new ArgumentNullException("destinationDirectory");
            }
// ReSharper disable PossibleNullReferenceException
            string extension = Path.GetExtension(importDocument.FileName).ToLower();
// ReSharper restore PossibleNullReferenceException
            if (new[] {".xls", ".xlsx"}.Contains(extension) == false)
            {
                throw new ArgumentException("import document is not an Excel file", "importDocument");
            }

            if (!Directory.Exists(destinationDirectory))
            {
                Directory.CreateDirectory(destinationDirectory);
            }

// ReSharper disable PossibleNullReferenceException
            var fileName = Path.GetFileNameWithoutExtension(importDocument.FileName).Replace(" ", string.Empty);
// ReSharper restore PossibleNullReferenceException

            var fileExtension = extension;
            string destinationFile = null;
            for (int x = 0; x < 1000; x++)
            {
                var uniqueFileName = string.Format("{0}{1}{2}", fileName, (x == 0 ? string.Empty : "-" + x),
                                                   fileExtension);
                destinationFile = Path.Combine(destinationDirectory, uniqueFileName);
                if (!File.Exists(destinationFile))
                {
                    importDocument.SaveAs(destinationFile);
                    break;
                }
            }
            return destinationFile;
        }
    }
}